package cn.dai.dao;

import cn.dai.pojo.User;
import cn.dai.util.JdbcUtils;
import com.sun.imageio.plugins.common.I18N;

import java.util.List;

/**
 * @author ArkD42
 * @file Rbac
 * @create 2020 - 06 - 11 - 9:31
 */
public class UserDaoImpl implements UserDao{

    @Override
    public int deleteUserByIds(Integer[] ids) {

        StringBuffer incompleteSql = new StringBuffer("DELETE FROM user WHERE u_id IN (");

        for (int i = 0; i < ids.length ; i++) {
            if (i == ids.length - 1){
                incompleteSql.append("?)");
                break;
            }
            incompleteSql.append("?, ");
        }

        final String SQL = incompleteSql.toString();

        return JdbcUtils.update(
                JdbcUtils.getConnection(),
                SQL,
                ids
        );
    }

    @Override
    public User queryUserById(Integer id) {
        final String sql = "SELECT * FROM user WHERE u_id = ?";

        return JdbcUtils.queryOne(
                JdbcUtils.getConnection(),
                User.class,
                sql,
                new Object[]{id}
        );
    }

    @Override
    public User queryUserByInfo(String name, String password) {
        String sql = new StringBuffer("SELECT * FROM user ").
                append("WHERE u_name = ? AND u_password = ?").
                toString();

        return JdbcUtils.queryOne(
                JdbcUtils.getConnection(),
                User.class,
                sql,
                new Object[]{name,password}
        );
    }

    @Override
    public int deleteUserBy(Integer id) {
        String sql = "DELETE FROM user WHERE u_id = ?";

        return JdbcUtils.update(
                JdbcUtils.getConnection(),
                sql,
                new Object[]{id}
        );
    }

    @Override
    public int insertUser(User user) {
        String sql = new StringBuffer("INSERT INTO user ").
                append("VALUES(DEFAULT, ?, ?, ?, ?, ?)").
                toString();

        return JdbcUtils.update(
                JdbcUtils.getConnection(),
                sql,
                new Object[]{
                        user.getU_name(),
                        user.getU_password(),
                        user.getU_email(),
                        user.getU_phone(),
                        user.getU_status()
                }
        );
    }

    @Override
    public int updateUserById(User user) {
        String sql = new StringBuffer("UPDATE user SET ").
                append("u_name = ?, u_password = ?, u_email = ?, u_phone = ?, u_status = ? ").
                append("WHERE u_id = ?").
                toString();

        return JdbcUtils.update(
                JdbcUtils.getConnection(),
                sql,
                new Object[]{
                        user.getU_name(),
                        user.getU_password(),
                        user.getU_email(),
                        user.getU_phone(),
                        user.getU_status(),
                        user.getU_id()
                }
        );
    }

    @Override
    public List<User> queryUserByIds(List<Integer> idList) {
        StringBuffer half_sql = new StringBuffer("SELECT * FROM user WHERE u_id in (");

        for (int i = 0; i < idList.size() ; i++) {
            if (i == idList.size() - 1){
                half_sql.append("?");
                break;
            }
            half_sql.append("?, ");
        }

        String sql = half_sql.append(")").toString();

        // 通过遍历注入到参数数组中
        Object[] arguments = new Object[idList.size()];
        for (int i = 0; i < arguments.length ; i++) {
            arguments[i] = idList.get(i);
        }

        return JdbcUtils.queryToList(
                JdbcUtils.getConnection(),
                User.class,
                sql,
                arguments
        );
    }

    @Override
    public Long queryUserTotalCounts() {

        String sql = "SELECT COUNT(1) FROM user";

        return JdbcUtils.getValue(
                JdbcUtils.getConnection(),
                sql,
                null
        );
    }

    @Override
    public List<User> queryUserByLimit(Integer index, Integer size) {

        String sql = "SELECT * FROM user LIMIT ?, ?";

        return JdbcUtils.queryToList(
                JdbcUtils.getConnection(),
                User.class,
                sql,
                new Object[]{index,size}
        );
    }

    @Override
    public List<User> queryUsersByLike(String keywords) {

        // 服了JDBC，这个预编译模糊查询就是需要自己来拼通配符
        final String escapeKeywords = "%" + keywords + "%";

        StringBuffer incompleteSql = new StringBuffer("SELECT * FROM user WHERE ").
                append("u_name LIKE ? OR ").
                append("u_password LIKE ? OR ").
                append("u_email LIKE ? OR ").
                append("u_phone LIKE ? ");

        final String SQL = incompleteSql.toString();

        return JdbcUtils.queryToList(
                JdbcUtils.getConnection(),
                User.class,
                SQL,
                new Object[]{
                        escapeKeywords,
                        escapeKeywords,
                        escapeKeywords,
                        escapeKeywords,
                }
        );
    }

    @Override
    public Long queryUserColumnCount() {
        // TABLE_SCHEMA='rbac' 表述数据库名称 table_name='user' 表示数据表名称
        final String SQL = "SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='rbac' and table_name='user'";
        return JdbcUtils.getValue(
                JdbcUtils.getConnection(),
                SQL,
                null
        );
    }



}
